package test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * 
 * @author shefali
 * In all the functions of this class a connection to the database is achieved via a DriverManager class
 * Once the JDBC drivers are loaded, getConnection method is applied to the Driver Manager class using the
 * Appropriate url and username, password
 * 
 * Prepared statement is used to send SQL statements to the database server since it is more efficient than the
 * Statement object
 * The ResultSet class is used to access the table of data generated by executing the sql queries
 *
 */

public class ListOfAttendees {
	
	/**
	 * 
	 * @param confName
	 * @return List containing details of the participants
	 * @throws SQLException
	 * @throws IOException
	 */
	public List<Participant> getParticipantDetails(String confName)
	throws SQLException, IOException {
Connection conn = null;

List<Participant> participantDetails = new ArrayList<Participant>();
PreparedStatement pstmt = null;
ResultSet rset;
try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
	String user, pass;
	user = "skakodkar";
	pass = "p73505";

	conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
			+ pass + "@oraserver.cs.luc.edu:1521:cs");

	String query = "select fname,lname,email,title,company from participant p,registration r where p.participantid = r.participantid and attendeeList ='yes' and r.conferenceid in(select conferenceid from conference where confname = ?)";
	pstmt = conn.prepareStatement(query);
	pstmt.setString(1, confName);
	rset = pstmt.executeQuery();
	while (rset.next()) {
		Participant p = new Participant();
		p.setFname(rset.getString(1));
		p.setLname(rset.getString(2));
		p.setEmail(rset.getString(3));
		p.setTitle(rset.getString(4));
		p.setCompany(rset.getString(5));
		participantDetails.add(p);
	}

} catch (Exception e) {
	e.printStackTrace();
} finally {
	conn.close();
}
return participantDetails;

	}

}
